package Models;

import java.sql.Connection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import utils.GetConnection;

public class Customer {
	
		private int id;
		private String firstName;
		private String lastName;
		private String phoneNumber;
		private String emailAddress;
		private Date birthDate;
		private String address;
		private String city;
		private String country; 
		private String notes;
		
		public Customer() {
			
		}
		
		
		public Customer(int id, String firstName, String lastName, String phoneNumber, String emailAddress,
				Date birthDate, String address, String city, String country, String notes) {
			this.id = id;
			this.firstName = firstName;
			this.lastName = lastName;
			this.phoneNumber = phoneNumber;
			this.emailAddress = emailAddress;
			this.birthDate = birthDate;
			this.address = address;
			this.city = city;
			this.country = country;
			this.notes = notes;
		}
		
		public Customer(long id){
			PreparedStatement stm = null;
			try {
				String req = "select * from customers where id = ?;";
				stm = GetConnection.getConnection().prepareStatement(req);			
				stm.setLong(1, id);
				ResultSet res = stm.executeQuery();
				if(res.next()){
					this.id = res.getInt("id");
					firstName = res.getString("first_name");
					lastName = res.getString("last_name");
					phoneNumber = res.getString("phone_number");
					emailAddress = res.getString("email");
					birthDate = res.getDate("birth_date");
					address = res.getString("address");
					city = res.getString("city");
					country = res.getString("country");
					notes = res.getString("notes");
				}
			}
			catch (SQLException e) {e.printStackTrace();}
			finally{
				try {
					if(stm != null) stm.close();
				} catch (SQLException e) {}
			}
		}
		
		public static ArrayList<Customer> LoadSql() {
			return LoadSql("");
		}
		
		public static Customer LoadSql(int id) {
			String sqlWhere =  "id = " + id;
			ArrayList<Customer> l = LoadSql(sqlWhere);
			if(l == null || l.size() == 0)
				return null;
			else
				return l.get(0);
		}
			
		public static ArrayList<Customer> LoadSql(String sqlWhere) {
			int id;
			ArrayList<Customer> customers = new ArrayList<Customer>();
			
			Connection conn = null;
			Statement stmt = null;
			
			try {
				conn = GetConnection.getConnection();
				stmt = null;
				ResultSet rs = null;
				stmt = conn.createStatement();
				if(sqlWhere != "") sqlWhere = "WHERE "+sqlWhere;
				rs = stmt.executeQuery("SELECT * FROM customers " + sqlWhere +";");
				
				while(rs.next()) {
					id = rs.getInt("id");
					customers.add(new Customer(id));
				}
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					stmt.close();
				
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return customers;
		}
		
		public static void Save(Customer customer) {
			Connection conn = CustomConnection.getConnection();
			PreparedStatement stmt = null;
			
			try {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				}
				catch (Exception e) { System.out.println(e); }
				

				stmt = conn.prepareStatement("INSERT INTO CUSTOMERS (first_name, last_name, phone_number, email, birth_date, address, city, country, notes) " +
						"VALUES (?,?,?,?,?,?,?,?,?)");
				stmt.setString(1, customer.getFirstName());
				stmt.setString(2, customer.getLastName());
				stmt.setString(3, customer.getPhoneNumber());
				stmt.setString(4, customer.getEmailAddress());
				stmt.setDate(5, new java.sql.Date(customer.getBirthDate().getTime()));
				stmt.setString(6, customer.getAddress());
				stmt.setString(7, customer.getCity());
				stmt.setString(8, customer.getCountry());
				stmt.setString(9, customer.getNotes());
				
				stmt.executeUpdate();
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		
		public static void delete(Provider customer) {
			Connection conn = CustomConnection.getConnection();
			
			try {
				try {
					Class.forName("com.mysql.jdbc.Driver");
				}
				catch (Exception e) { System.out.println(e); }
				
				Statement stmt = null;
				
				stmt = conn.createStatement();
				System.out.println("teeest");
				stmt.executeQuery("DELETE FROM CUSTOMERS WHERE id = '" + customer.getId());
			}
			catch (SQLException e) {
				System.out.println(e);
			}
			finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		public int getId() {
			return id;
		}

		public void setId(int id) {
			this.id = id;
		}

		public String getName() {
			return firstName;
		}

		public void setName(String name) {
			this.firstName = name;
		}

		public String getSurname() {
			return lastName;
		}

		public void setSurname(String surname) {
			this.lastName = surname;
		}

		public String getPhoneNumber() {
			return phoneNumber;
		}

		public void setPhoneNumber(String phoneNumber) {
			this.phoneNumber = phoneNumber;
		}

		public String getEmailAddress() {
			return emailAddress;
		}

		public void setEmailAdress(String emailAdress) {
			this.emailAddress = emailAdress;
		}

		public String getFirstName() {
			return firstName;
		}

		public void setFirstName(String firstName) {
			this.firstName = firstName;
		}

		public String getLastName() {
			return lastName;
		}

		public void setLastName(String lastName) {
			this.lastName = lastName;
		}

		public Date getBirthDate() {
			return birthDate;
		}

		public void setBirthDate(Date date) {
			this.birthDate = date;
		}

		public String getAddress() {
			return address;
		}

		public void setAddress(String address) {
			this.address = address;
		}

		public String getCity() {
			return city;
		}

		public void setCity(String city) {
			this.city = city;
		}

		public String getCountry() {
			return country;
		}

		public void setCountry(String country) {
			this.country = country;
		}


		public static void delete(Customer customer) {
			Connection conn = CustomConnection.getConnection();
			
			try {
				Class.forName("com.mysql.jdbc.Driver");
		
				Statement stmt = null;				
				stmt = conn.createStatement();
				stmt.executeUpdate("DELETE FROM customers WHERE id = " + customer.getId());
			}
			catch (Exception e) {
				System.out.println(e.getMessage());
			}
			finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}


		public String getNotes() {
			return notes;
		}


		public void setNotes(String notes) {
			this.notes = notes;
		}


		public static void Update(Customer customer) {
			String req = "update customers set first_name = ?, last_name = ?, phone_number = ?, email = ?, Address = ?, city = ?, " +
					"country = ?, notes= ? where id = ?";
			PreparedStatement stm = null;
			System.out.println("eeedit "+customer.notes);
			try {
				stm = GetConnection.preparedStatement(req);
				stm.setString(1, customer.firstName);
				stm.setString(2, customer.lastName);
				stm.setString(3, customer.phoneNumber);
				stm.setString(4, customer.emailAddress);
				stm.setString(5, customer.address);
				stm.setString(6, customer.city);
				stm.setString(7, customer.country);
				stm.setString(8, customer.notes);
				stm.setInt(9, customer.id);
				stm.executeUpdate();
			} 
			catch (SQLException e) {e.printStackTrace();}
			finally{
				try { if(stm != null) stm.close(); }
				catch (SQLException e) {}
			}
			
		}
		
		public static List<Customer> search(String firstName, String lastName, String ageMin, String ageMax, String city,
				String country) {
			List<Customer> customers = new ArrayList<Customer>();
			String req = "SELECT * FROM customers where 1=1 ";
			int nbParam = 0;
			PreparedStatement stm = null;
			Map<Integer, Object> values = new HashMap<Integer, Object>();
			
			if(firstName != null && !firstName.equals("")) {
				req += "AND first_name LIKE concat('%',?,'%') ";	
				values.put(++nbParam, firstName);
			}
			if(lastName != null && !lastName.equals("")) {
				req += "AND last_name LIKE concat('%',?,'%') ";
				values.put(++nbParam, lastName);
			}
			
			if(ageMax != null && ageMin != null && !ageMax.equals("") && !ageMin.equals("") ) {
				int min = Integer.parseInt(ageMin);
				int max = Integer.parseInt(ageMax);
				if((min < max) && (min >= 0 || max >= 0)) {
					req += "AND (birth_date >= NOW() - INTERVAL ? YEAR AND birth_date < NOW() - INTERVAL ? YEAR) ";
					values.put(++nbParam, Integer.valueOf(max));
					values.put(++nbParam, Integer.valueOf(min));
				}
			}
			if(city != null && !city.equals("") ) {
				req += "AND city LIKE concat('%',?,'%') ";
				values.put(++nbParam, city);
			}
			if(country != null && !country.equals("") ) {
				req += "AND country LIKE concat('%',?,'%') ";
				values.put(++nbParam, country);
			}
			try {
				System.out.println(req);
				stm = GetConnection.preparedStatement(req);
				Iterator<Integer> i = values.keySet().iterator();
				while(i.hasNext()){
					Integer pos = i.next();
					Object val = values.get(pos);
					if(val instanceof String) {
						System.out.println("1,p:"+pos+":"+val);
						stm.setString(pos, (String) val);
					} else {
						System.out.println("2,p"+pos+":"+val);
						stm.setInt(pos, (Integer) val);
					}
				}
				
			ResultSet rs = stm.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				customers.add(new Customer(id));
			}
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return customers;
		}
}

